#!/usr/bin/env python
# -*- coding: utf-8 -*-
# SQL type wrappers audited by Joshua Kugler, 2008-02-20
"""
This is a library API, making no direct access to the database
except for the tmpo_schedule table. Please see any of the other
schedule_* files for database details.

functions found in this API are:

  build_row_dict
  build_empty_row_dict
  fill_dict_from_form
  build_row_dict_for_sql
  build_columns_values_sql
  sql_string
  sql_int
  sql_date
  sql_bool
  resources_provided
  resources_valid  
  make_available_vehicle_list  
  make_available_driver_list
  set_to_conflicted
  
    All of these functions are 'utility' routines, designed
    to manage data for output - either by coordinating dictionary
    key/value pairs or by stripping out invalid/unwanted characters.
"""
from string import Template, join
from tack.db import sql_date as tdb_sql_date, sql_int as tdb_sql_int
from tack.lib import string_app, content_headers, status_codes
from tack.lib.jsontools import json_dumps
from datetime import datetime
from drivers import driver_availability
from vehicles import select_available_vehicles

class state_def:
    REQUESTED, CONFLICTED, RESOURCED, CANCELED = range(1,5)


select_one_resourced = """
SELECT
    *
FROM
    tmpo_schedule
WHERE(
    state_finish IS NULL 
    AND id = $record_id 
    AND state_key = 3)
  OR(
    state_finish IS NULL 
    AND parent_id = $record_id
    AND state_key = 3)
"""

set_state_finish = """
UPDATE
    tmpo_schedule
SET
    state_finish = '$time_stamp'
WHERE
    id = $state_id
"""

insert_state_sql = "INSERT INTO tmpo_schedule"


def build_row_dict(cd, row):
    d = {}
    for i, c in enumerate(row):
        if cd[i][1] == datetime:
            row[i] = row[i].strftime('%Y-%m-%d %H:%M:%S') if row[i] else None
        d[cd[i][0]] = row[i]
    return d


def build_empty_row_dict(cd):
    d = {}
    for i, c in enumerate(cd):
        d[cd[i][0]] = None
    return d


def fill_dict_from_form(d, cd, form):
    for i, c in enumerate(cd):
        name = cd[i][0]
        if form.has_key(name):
            v = form[name]
            type = cd[i][1]
            encoder = type_map_form.get(type, type)
            try:
                v = encoder(v)
            except (Exception, ), ex:
                raise Exception(str(ex) + " || " + str(name) + " || " + str(v))
            d[name] = v
    return d


def build_row_dict_for_sql(cd, row):
    d = {}
    for i, c in enumerate(row):
        name = cd[i][0]
        if row[i] == None:
            v = None
        else:
            v = row[i]
            type = cd[i][1]
            encoder = type_map.get(type, type)
            v = encoder(v)      
        d[name] = v
    return d


def build_columns_values_sql(d):
    for k, v in d.iteritems():
        if v == None:
            d[k] = 'NULL'
    columns_string = ' (' + ', '.join(str(k) for k, v in d.iteritems()) + ')'
    values_string = ' (' + ', '.join(str(v) for k, v in d.iteritems()) + ')'
    return columns_string + ' VALUES' + values_string


def sql_string(val):
    val = val.replace("'", "''")
    return "'%s'" % val

def sql_int(val):
    val = int(val)
    return "%s" % val

def sql_date(val):
    val = val.strftime('%Y-%m-%d %H:%M:%S')
    return "'%s'" % val

def sql_bool(val):
    if val == True:
        return '1'
    return '0'


def resources_provided(form, cursor):
    field_list = ['start','finish','vehicle_id','driver_required']
    for field in field_list:
        if not form.has_key(field):
            return False
    if bool(int(form['driver_required'])):
        if not form.has_key('driver_id'):
            return False
    return True


def resources_valid(form, cursor, key_to_exclude):
    field_list = ['start','finish','vehicle_id','driver_required']
    for field in field_list:
        if not form.has_key(field):
            return False
    vehicle_list = make_available_vehicle_list(
        tdb_sql_date(form['start'], '%Y-%m-%d %H:%M').replace("'", ""),
        tdb_sql_date(form['finish'],'%Y-%m-%d %H:%M').replace("'", ""),
        cursor, key_to_exclude)
    vehicle = tdb_sql_int(form['vehicle_id'])
    if not vehicle in vehicle_list:
        return False
    if bool(int(form['driver_required'])):
        if not form.has_key('driver_id'):
            return False
        driver_list = make_available_driver_list(
            tdb_sql_date(form['start'], '%Y-%m-%d %H:%M').replace("'", ""),
            tdb_sql_date(form['finish'], '%Y-%m-%d %H:%M').replace("'", ""),
            cursor,key_to_exclude)
        driver = tdb_sql_int(form['driver_id'])
        if not driver in driver_list:
            return False
    return True


def make_available_vehicle_list(start, finish, cursor, record_id):
    sql = Template(select_available_vehicles).substitute(locals())
    cursor.execute(sql)
    rows = cursor.fetchall()
    list = []
    for row in rows:
        list.append(row[0])
    return list


def make_available_driver_list(start, finish, cursor, record_id):
    sql = Template(driver_availability).substitute(locals())
    cursor.execute(sql)
    rows = cursor.fetchall()
    list = []
    for row in rows:
        list.append(row[0])
    return list


def set_to_conflicted(record_id, environ):
    cursor = environ['connection'].cursor()
    sql = Template(select_one_resourced).substitute(locals())
    cursor.execute(sql)
    rows = cursor.fetchall()
    if rows:
        state_id = rows[0].id
        throwaway = datetime.now()
        time_stamp = throwaway.strftime('%Y-%m-%d %H:%M:%S')
        state_dict = build_row_dict_for_sql(cursor.description, rows[0])
        if not state_dict['parent_id']:
            state_dict['parent_id'] = state_dict['id']
        del state_dict['id']
        state_dict['state_start'] = "'" + time_stamp + "'"
        del state_dict['state_finish']
        state_dict['state_key'] = str(state_def.CONFLICTED)
        del state_dict['driver_id']
        del state_dict['vehicle_id']
        sql = insert_state_sql + build_columns_values_sql(state_dict)
        cursor.execute(sql)
        environ['connection'].commit()
        sql = Template(set_state_finish).substitute(locals())
        cursor.execute(sql)
        environ['connection'].commit()
    return True

## TODO:  migrate into new db library when library becomes available for use
type_map = {
    str: sql_string,
    int: sql_int,
    datetime: sql_date,
    bool: sql_bool,
    }

type_map_form = {
    str: sql_string,
    int: sql_int,
    datetime: sql_string,
    bool: sql_int,
    }
